--region [dbo].[GetLatestPatientList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLatestPatientList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLatestPatientList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLatestPatientList]
GO

CREATE PROCEDURE [dbo].[GetLatestPatientList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([userinfo].[Id]) 
FROM 
	[userinfo]
WHERE 
 [IsActive] > 0
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[userinfo].[Id],
	[UserName],
	[Email],
	[Address],
	[Gender],
	[Avatar],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[userinfo].[Description],
	[ViewCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM 
	[userinfo]

WHERE 
[IsActive] = 1

	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion
--region [dbo].[GetPatientList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetPatientList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientList]
GO

CREATE PROCEDURE [dbo].[GetPatientList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([userinfo].[Id]) 
FROM 
	[userinfo], [PatientRecord]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[userinfo].[Id],
	[UserName],
	[Email],
	''Province'' = dbo.GetDistrictName([ProvinceId]),
	''City'' = dbo.GetDistrictName([CityId]),
	[Address],
	''Age'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[Gender],
	[Avatar],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[userinfo].[Description],
	[ViewCount],
	''DiseaseId'' = [Disease].[Id],
	[DiseaseName],
	''EnduringYear'' = DATEDIFF(year, [PatientRecord].[StartDate], getdate()),
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM 
	[userinfo], [PatientRecord], [Disease]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] = 1
	AND [PatientRecord].[DiseaseId] = [Disease].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetPatientListBySymptom]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientListBySymptom]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetPatientListBySymptom]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientListBySymptom]
GO

CREATE PROCEDURE [dbo].[GetPatientListBySymptom]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([userinfo].[Id]) 
FROM 
	[userinfo], [PatientRecord], [PatientCurrentSymptom]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	AND [PatientCurrentSymptom].[PRId] = [PatientRecord].[Id]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[userinfo].[Id],
	[UserName],
	[Email],
	''Province'' = dbo.GetDistrictName([ProvinceId]),
	''City'' = dbo.GetDistrictName([CityId]),
	[Address],
	''Age'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[Gender],
	[Avatar],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[userinfo].[Description],
	[ViewCount],
	''DiseaseId'' = [Disease].[Id],
	[DiseaseName],
	''EnduringYear'' = DATEDIFF(year, [PatientRecord].[StartDate], getdate()),
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM 
	[userinfo], [PatientRecord], [Disease], [PatientCurrentSymptom]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	AND [PatientCurrentSymptom].[PRId] = [PatientRecord].[Id]
	AND [PatientRecord].[DiseaseId] = [Disease].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetPatientListByTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientListByTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetPatientListByTreatment]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientListByTreatment]
GO

CREATE PROCEDURE [dbo].[GetPatientListByTreatment]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([userinfo].[Id]) 
FROM 
	[userinfo], [PatientRecord], [PatientCurrentTreatment]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	AND [PatientCurrentTreatment].[PRId] = [PatientRecord].[Id]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[userinfo].[Id],
	[UserName],
	[Email],
	''Province'' = dbo.GetDistrictName([ProvinceId]),
	''City'' = dbo.GetDistrictName([CityId]),
	[Address],
	''Age'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[Gender],
	[Avatar],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[userinfo].[Description],
	[ViewCount],
	''DiseaseId'' = [Disease].[Id],
	[DiseaseName],
	''EnduringYear'' = DATEDIFF(year, [PatientRecord].[StartDate], getdate()),
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM 
	[userinfo], [PatientRecord], [Disease], [PatientCurrentTreatment]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	AND [PatientCurrentTreatment].[PRId] = [PatientRecord].[Id]
	AND [PatientRecord].[DiseaseId] = [Disease].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetPatientListByAdvanceSearch]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetPatientListByAdvanceSearch]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetPatientListByAdvanceSearch]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetPatientListByAdvanceSearch]
GO

CREATE PROCEDURE [dbo].[GetPatientListByAdvanceSearch]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([userinfo].[Id]) 
FROM 
	[userinfo], [PatientRecord], [PatientCurrentTreatment], [PatientCurrentSymptom]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	AND [PatientCurrentTreatment].[PRId] = [PatientRecord].[Id]
	AND [PatientCurrentSymptom].[PRId] = [PatientRecord].[Id]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[userinfo].[Id],
	[UserName],
	[Email],
	''Province'' = dbo.GetDistrictName([ProvinceId]),
	''City'' = dbo.GetDistrictName([CityId]),
	[Address],
	''Age'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[Gender],
	[Avatar],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[userinfo].[Description],
	[ViewCount],
	''DiseaseId'' = [Disease].[Id],
	[DiseaseName],
	''EnduringYear'' = DATEDIFF(year, [PatientRecord].[StartDate], getdate()),
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM 
	[userinfo], [PatientRecord], [Disease], [PatientCurrentTreatment], [PatientCurrentSymptom]
WHERE 
	[userinfo].[Id] = [PatientRecord].[PatientId] AND [IsActive] > 0
	AND [PatientCurrentTreatment].[PRId] = [PatientRecord].[Id]
	AND [PatientCurrentSymptom].[PRId] = [PatientRecord].[Id]
	AND [PatientRecord].[DiseaseId] = [Disease].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[Addpatientrecord]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Addpatientrecord]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Addpatientrecord]') IS NOT NULL
	DROP PROCEDURE [dbo].[Addpatientrecord]
GO

CREATE PROCEDURE [dbo].[Addpatientrecord]
	@patientid int,
	@diseaseid int,
	@recorddate datetime,
	@updatedate datetime,
	@remark nvarchar(max),
	@startdate datetime,
	@ConfirmDate datetime,
	@ConfirmHospital nvarchar(50),
	@UpdateTime int
AS

INSERT INTO [patientrecord] (
	[patientid],
	[diseaseid],
	[recorddate],
	[updatedate],
	[remark],
	[startdate],
	[ConfirmDate],
	[ConfirmHospital],
	[UpdateTime]
) VALUES (
	@patientid,
	@diseaseid,
	@recorddate,
	@updatedate,
	@remark,
	@startdate,
	@ConfirmDate,
	@ConfirmHospital,
	@UpdateTime
)

GO
--endregion


--region [dbo].[Updatepatientrecord]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Updatepatientrecord]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Updatepatientrecord]') IS NOT NULL
	DROP PROCEDURE [dbo].[Updatepatientrecord]
GO

CREATE PROCEDURE [dbo].[Updatepatientrecord]
	@id int,
	@diseaseid int,
	@updatedate datetime,
	@remark nvarchar(max),
	@startdate datetime,
	@ConfirmDate datetime,
	@ConfirmHospital nvarchar(50)
AS

UPDATE [patientrecord] SET
	[diseaseid] = @diseaseid,
	[updatedate] = @updatedate,
	[remark] = @remark,
	[startdate] = @startdate,
	[ConfirmDate] = @ConfirmDate,
	[ConfirmHospital] = @ConfirmHospital
WHERE
	[id] = @id

GO
--endregion


--region [dbo].[Delpatientrecord]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Delpatientrecord]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Delpatientrecord]') IS NOT NULL
	DROP PROCEDURE [dbo].[Delpatientrecord]
GO

CREATE PROCEDURE [dbo].[Delpatientrecord]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[patientrecord]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[Getpatientrecord]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Getpatientrecord]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Getpatientrecord]') IS NOT NULL
	DROP PROCEDURE [dbo].[Getpatientrecord]
GO

CREATE PROCEDURE [dbo].[Getpatientrecord]
   @WhereCondition nvarchar(512)
AS
DECLARE @strSQL nvarchar(1024)

set @strSQL = '
SELECT
	[id],
	[patientid],
	[diseaseid],
	[recorddate],
	[updatedate],
	[UpdateTime],
	[remark],
	[startdate],
	[enddate],
	[ConfirmDate],
	[ConfirmHospital]
FROM
	[patientrecord]
WHERE
' + @WhereCondition

EXEC sp_executesql @strSQL

GO
--endregion


/***************************************************************
AddSymptomRecordes
***************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddSymptomRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddSymptomRecordes
GO

create procedure AddSymptomRecordes
        @prid int
        ,@recorddate datetime
        ,@updatedate datetime
        ,@symptominfo nvarchar(2000)
        ,@remark nvarchar(max)
as
    insert into [patientsymptom]    (
       [prid]
       ,[recorddate]
       ,[updatedate]
       ,[symptominfo]
       ,[remark]
    )values( 
       @prid
       ,@recorddate
       ,@updatedate
       ,@symptominfo
       ,@remark
    )
GO
  

/**********************************************************************
UpdateSymptomRecordes
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateSymptomRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateSymptomRecordes
GO

create procedure UpdateSymptomRecordes
     @id int
        ,@prid int
        ,@recorddate datetime
        ,@updatedate datetime
        ,@symptominfo nvarchar(2000)
        ,@remark nvarchar(max)
as
    update [patientsymptom]  set 
       [prid] = @prid
       ,[recorddate] = @recorddate
       ,[updatedate] = @updatedate
       ,[symptominfo] = @symptominfo
       ,[remark] = @remark
     where [id] = @id
GO  



/**********************************************************************
GetSymptomRecordes
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetSymptomRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetSymptomRecordes
GO

create procedure GetSymptomRecordes
   @id int
as
  
  select top 1 
      'Id' = [patientsymptom].[id] 
      ,'PatientRecordId' = [patientsymptom].[prid]
      ,'RecordDate' = [patientsymptom].[recorddate]
      ,'UpdateDate' = [patientsymptom].[updatedate]
      ,'SymptomInfo' = [patientsymptom].[symptominfo]
      ,'Remark' = [patientsymptom].[remark]
   from [patientsymptom]
   where [id] = @id   

  
GO


/**********************************************************************
  DelSymptomRecordes
**********************************************************************/  
         
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelSymptomRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelSymptomRecordes
GO

create procedure DelSymptomRecordes
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'delete from [patientsymptom] where ' + @condstr
   exec(@strSQL)
GO

/****************************************************************
GetSymptomRecordesList
*****************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetSymptomRecordesList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetSymptomRecordesList
GO

create procedure GetSymptomRecordesList
   @pagesize int,
   @pageindex int,
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   
         set @strSQL = 'select count([id]) from [patientsymptom]'
         if @condstr <> ''
            begin
                set @strSQL = @strSQL + ' where ' + @condstr
            end     
         exec(@strSQL)
         
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
          ''Id'' = [patientsymptom].[id]
         ,''RecordDate'' = [patientsymptom].[recorddate]
         ,''UpdateDate'' = [patientsymptom].[updatedate]
         ,''SymptomInfo'' = [patientsymptom].[symptominfo]
         ,''Remark'' = [patientsymptom].[remark] 
      from 
         [patientsymptom] '

      if @pageindex > 1
      begin
       set @strSQL = @strSQL + '
         where [disease].[id] < 
          ( 
            SELECT   min([patientsymptom].[id])  
            FROM ( 
               SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [patientsymptom].[id] 
               FROM [patientsymptom] ' 

       if @condstr <> ''
       begin
           set @strSQL = @strSQL + 'where ' + @condstr
       end

       set @strSQL = @strSQL + 
         ' ORDER BY [patientsymptom].[recorddate] DESC) AS tblTmp ) '

       if @condstr <> ''
       begin
           set @strSQL = @strSQL + 'and ' + @condstr
       end

     end
     else 
     begin

      if @condstr <> ''
        set @strSQL = @strSQL + 'where ' + @condstr
    
     end

   set @strSQL = @strSQL  + 'ORDER BY [patientsymptom].[recorddate] DESC'

   exec(@strSQL)
   
GO


/***************************************************************
****************************************************************
****************************************************************
****************************************************************
TreatmentRecordes
****************************************************************
****************************************************************
****************************************************************
***************************************************************/

/**********************************************************************
AddTreatmentRecord
***********************************************************************/
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'AddTreatmentRecord') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure AddTreatmentRecord
GO

create procedure AddTreatmentRecord
		@prid int
		,@treatmentid int
		,@datavalue float
		,@unit nvarchar(50)
		,@frequency nvarchar(50)
		,@state int
		,@startdate datetime
		,@enddate datetime
		,@updatedate datetime
		,@usepurpose int
		,@stopreason int
		,@privacy int
as
    insert into [patienttreatment]    (
       [prid]
       ,[treatmentid]
       ,[datavalue]
	   ,[unit]
	   ,[frequency]
	   ,[state]
       ,[startdate]
	   ,[enddate]
       ,[updatedate]
       ,[usepurpose]
       ,[stopreason]
       ,[privacy]
    )values( 
       @prid
       , @treatmentid
       , @datavalue
	   , @unit
	   , @frequency
	   , @state
       , @startdate
	   , @enddate
       , @updatedate
       , @usepurpose
       , @stopreason
       , @privacy
    )
GO

/**********************************************************************
UpdateTreatmentRecord
***********************************************************************/
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'UpdateTreatmentRecord') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure UpdateTreatmentRecord
GO

create procedure UpdateTreatmentRecord
		@id int
		,@datavalue float
		,@unit nvarchar(50)
		,@frequency nvarchar(50)
		,@startdate datetime
		,@enddate datetime
		,@updatedate datetime
		,@usepurpose int
		,@stopreason int
		,@privacy int
as
	update [patienttreatment]  set 
		[datavalue] = @datavalue
		,[unit] = @unit
		,[frequency] = @frequency
		,[startdate] = @startdate
		,[enddate] = @enddate
		,[updatedate] = @updatedate
		,[usepurpose] = @usepurpose
		,[stopreason] = @stopreason
		,[privacy] = @privacy
		where [id] = @id
GO

/**********************************************************************
StopTreatmentRecord
***********************************************************************/
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'StopTreatmentRecord') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure StopTreatmentRecord
GO

create procedure StopTreatmentRecord
		@id int
		,@enddate datetime
		,@updatedate datetime
		,@stopreason int
		,@state	int
as
	update [patienttreatment]  set 
		[enddate] = @enddate
		,[stopreason] = @stopreason
		,[updatedate] = @updatedate
		,[state] = @state
		where [id] = @id
GO

/**********************************************************************
  DelTreatmentRecordes
**********************************************************************/  
         
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelTreatmentRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelTreatmentRecordes
GO

create procedure DelTreatmentRecordes
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(1024)
   set @strSQL = 'delete from [patienttreatment] where ' + @condstr
   exec(@strSQL)
GO

/***********************************************************************************
GetTreatmentRecorde
***********************************************************************************/

if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'GetTreatmentRecorde') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure GetTreatmentRecorde
GO

create procedure GetTreatmentRecorde
	@id int
as
	select top 1
		'Id' = [patienttreatment].[id]
		,'PRId' = [patienttreatment].[prid]
		,'TreatmentId' = [patienttreatment].[treatmentid]
		,'TreatmentName' = [treatment].[treatmentname]
		,'StartDate' = [patienttreatment].[startdate]
		,'EndDate' = [patienttreatment].[enddate]
		,'State' = [patienttreatment].[state]
		,'UsePurpose' = [patienttreatment].[usepurpose]
		,'StopReason' = [patienttreatment].[stopreason]
		,'DataValue' = [patienttreatment].[datavalue]
		,'Unit' = [patienttreatment].[unit]
		,'Frequency' = [patienttreatment].[frequency]
		,'Privacy' = [patienttreatment].[privacy]
	from 
		[patienttreatment] left join [treatment]
	on 
		[patienttreatment].[treatmentid] = [treatment].[id]
	where 
		[patienttreatment].[id]=@id 
Go

/**********************************************************************
  GetTreatmentRecordeList 
**********************************************************************/  
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'GetTreatmentRecordeList') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure GetTreatmentRecordeList
GO

create procedure GetTreatmentRecordeList
   @pagesize int,
   @pageindex int,
   @condstr nvarchar(1024)
as
	DECLARE @strSQL VARCHAR(1024)

	set @strSQL = 'select count([id]) from [patienttreatment]'
	if @condstr <> ''
		begin
			set @strSQL = @strSQL + ' where ' + @condstr
		end	    
	exec(@strSQL)
	 
	set @strSQL = '
	SELECT TOP ' + STR(@pagesize) + '
		''Id'' = [patienttreatment].[Id]
		,''TreatmentId'' = [patienttreatment].[treatmentid]
		,''TreatmentName'' = [treatment].[treatmentname]
		,''StartDate'' = [patienttreatment].[startdate]
		,''EndDate'' = [patienttreatment].[enddate]
		,''StopReason'' = [patienttreatment].[stopreason]
		,''UsePurpose'' = [patienttreatment].[usepurpose]
		,''State'' = [patienttreatment].[State]
		,''DataValue'' = [patienttreatment].[datavalue]
		,''Unit'' = [patienttreatment].[unit]
		,''Frequency'' = [patienttreatment].[frequency]
		,''UpdateDate'' = [patienttreatment].[updatedate] 
		,''Privacy'' = [patienttreatment].[privacy]
	from 
		[patienttreatment] left join [treatment]
	on
		[patienttreatment].[treatmentid]=[treatment].[id] '

	if @pageindex > 1
		begin
			set @strSQL = @strSQL + '
			where [patienttreatment].[id] < 
			(SELECT   min([patienttreatment].[id])  
			FROM ( 
			   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [patienttreatment].[id] 
			   FROM [patienttreatment] ' 

			if @condstr <> ''
			begin
				set @strSQL = @strSQL + ' where ' + @condstr
			end

			set @strSQL = @strSQL + 
				' ORDER BY [patienttreatment].[startdate] DESC) AS tblTmp ) '

			if @condstr <> ''
			begin
			   set @strSQL = @strSQL + ' and ' + @condstr
			end
		end
	else 
		begin
			if @condstr <> ''
				set @strSQL = @strSQL + ' where ' + @condstr
		end

	set @strSQL = @strSQL  + 'ORDER BY [patienttreatment].[startdate] DESC'

	exec(@strSQL)
   
GO


/***************************************************************
****************************************************************
****************************************************************
****************************************************************
LabcheckRecordes
****************************************************************
****************************************************************
****************************************************************
***************************************************************/

/***************************************************************
AddLabcheckRecordes
***************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddLabcheckRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddLabcheckRecordes
GO

create procedure AddLabcheckRecordes
        @prid int
        ,@recorddate datetime
        ,@updatedate datetime
        ,@checkinfo nvarchar(2000)
        ,@remark nvarchar(max)
as
    insert into [patientcheck]    (
       [prid]
       ,[recorddate]
       ,[updatedate]
       ,[checkinfo]
       ,[remark]
    )values( 
       @prid
       ,@recorddate
       ,@updatedate
       ,@checkinfo
       ,@remark
    )
GO
  

/**********************************************************************
UpdateLabcheckRecordes
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateLabcheckRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateLabcheckRecordes
GO

create procedure UpdateLabcheckRecordes
     @id int
        ,@prid int
        ,@recorddate datetime
        ,@updatedate datetime
        ,@checkinfo nvarchar(2000)
        ,@remark nvarchar(max)
as
    update [patientcheck]  set 
       [prid] = @prid
       ,[recorddate] = @recorddate
       ,[updatedate] = @updatedate
       ,[checkinfo] = @checkinfo
       ,[remark] = @remark
     where [id] = @id
GO  



/**********************************************************************
GetLabcheckRecordes
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetLabcheckRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetLabcheckRecordes
GO

create procedure GetLabcheckRecordes
   @id int
as
  
  select top 1 
      'Id' = [patientcheck].[id] 
      ,'PRId' = [patientcheck].[prid]
      ,'RecordDate' = [patientcheck].[recorddate]
      ,'UpdateDate' = [patientcheck].[updatedate]
      ,'LabcheckInfo' = [patientcheck].[checkinfo]
      ,'Remark' = [patientcheck].[remark]
   from [patientcheck]
   where [id] = @id   

  
GO


/**********************************************************************
  DelLabcheckRecordes
**********************************************************************/  
         
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelLabcheckRecordes') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelLabcheckRecordes
GO

create procedure DelLabcheckRecordes
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(1024)
   set @strSQL = 'delete from [patientcheck] where ' + @condstr
   exec(@strSQL)
GO

/****************************************************************
GetLabcheckRecordesList
*****************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetLabcheckRecordesList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetLabcheckRecordesList
GO

create procedure GetLabcheckRecordesList
   @pagesize int,
   @pageindex int,
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(1024)
   
         set @strSQL = 'select count([id]) from [patientcheck]'
         if @condstr <> ''
            begin
                set @strSQL = @strSQL + ' where ' + @condstr
            end     
         exec(@strSQL)
         
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
          ''Id'' = [patientcheck].[id]
         ,''PRId'' = [patientcheck].[prid]
         ,''RecordDate'' = [patientcheck].[recorddate]
         ,''UpdateDate'' = [patientcheck].[updatedate]
         ,''LabcheckInfo'' = [patientcheck].[checkinfo]
         ,''Remark'' = [patientcheck].[remark] 
      from 
         [patientcheck] '

      if @pageindex > 1
      begin
       set @strSQL = @strSQL + '
         where [disease].[id] < 
          ( 
            SELECT   min([patientcheck].[id])  
            FROM ( 
               SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [patientcheck].[id] 
               FROM [patientcheck] ' 

       if @condstr <> ''
       begin
           set @strSQL = @strSQL + 'where ' + @condstr
       end

       set @strSQL = @strSQL + 
         ' ORDER BY [patientcheck].[recorddate] DESC) AS tblTmp ) '

       if @condstr <> ''
       begin
           set @strSQL = @strSQL + 'and ' + @condstr
       end

     end
     else 
     begin

      if @condstr <> ''
        set @strSQL = @strSQL + 'where ' + @condstr
    
     end

   set @strSQL = @strSQL  + 'ORDER BY [patientcheck].[recorddate] DESC'

   exec(@strSQL)
   
GO
